
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 12/14/2011 00:59:02
-- Generated from EDMX file: D:\users\will\documents\visual studio 2010\Projects\ECSE6770\ECSE6770\Entities.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [ECSE6770];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_ModuleVenue]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Products_Module] DROP CONSTRAINT [FK_ModuleVenue];
GO
IF OBJECT_ID(N'[dbo].[FK_ModuleLevel]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Products_Module] DROP CONSTRAINT [FK_ModuleLevel];
GO
IF OBJECT_ID(N'[dbo].[FK_FeedbackUserInterop]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Feedback] DROP CONSTRAINT [FK_FeedbackUserInterop];
GO
IF OBJECT_ID(N'[dbo].[FK_ModuleSpeakers_Module]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ModuleSpeakers] DROP CONSTRAINT [FK_ModuleSpeakers_Module];
GO
IF OBJECT_ID(N'[dbo].[FK_ModuleSpeakers_UserInterop]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ModuleSpeakers] DROP CONSTRAINT [FK_ModuleSpeakers_UserInterop];
GO
IF OBJECT_ID(N'[dbo].[FK_FeedbackProduct]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Feedback] DROP CONSTRAINT [FK_FeedbackProduct];
GO
IF OBJECT_ID(N'[dbo].[FK_RelatedProducts_Product]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ProductProduct] DROP CONSTRAINT [FK_RelatedProducts_Product];
GO
IF OBJECT_ID(N'[dbo].[FK_RelatedProducts_Product1]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ProductProduct] DROP CONSTRAINT [FK_RelatedProducts_Product1];
GO
IF OBJECT_ID(N'[dbo].[FK_UserInteropProduct_UserInterop]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[UserInteropProduct] DROP CONSTRAINT [FK_UserInteropProduct_UserInterop];
GO
IF OBJECT_ID(N'[dbo].[FK_UserInteropProduct_Product]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[UserInteropProduct] DROP CONSTRAINT [FK_UserInteropProduct_Product];
GO
IF OBJECT_ID(N'[dbo].[FK_UserInteropOrderHistory]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[OrderHistories] DROP CONSTRAINT [FK_UserInteropOrderHistory];
GO
IF OBJECT_ID(N'[dbo].[FK_VideoEventVideo]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[VideoEvents] DROP CONSTRAINT [FK_VideoEventVideo];
GO
IF OBJECT_ID(N'[dbo].[FK_VideoEventUserInterop]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[VideoEvents] DROP CONSTRAINT [FK_VideoEventUserInterop];
GO
IF OBJECT_ID(N'[dbo].[FK_OrderProducts_OrderHistory]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[OrderHistoryProduct] DROP CONSTRAINT [FK_OrderProducts_OrderHistory];
GO
IF OBJECT_ID(N'[dbo].[FK_OrderProducts_Product]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[OrderHistoryProduct] DROP CONSTRAINT [FK_OrderProducts_Product];
GO
IF OBJECT_ID(N'[dbo].[FK_Module_inherits_Product]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Products_Module] DROP CONSTRAINT [FK_Module_inherits_Product];
GO
IF OBJECT_ID(N'[dbo].[FK_Video_inherits_Product]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Products_Video] DROP CONSTRAINT [FK_Video_inherits_Product];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Venues]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Venues];
GO
IF OBJECT_ID(N'[dbo].[Levels]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Levels];
GO
IF OBJECT_ID(N'[dbo].[UserInterops]', 'U') IS NOT NULL
    DROP TABLE [dbo].[UserInterops];
GO
IF OBJECT_ID(N'[dbo].[Feedback]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Feedback];
GO
IF OBJECT_ID(N'[dbo].[Products]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Products];
GO
IF OBJECT_ID(N'[dbo].[OrderHistories]', 'U') IS NOT NULL
    DROP TABLE [dbo].[OrderHistories];
GO
IF OBJECT_ID(N'[dbo].[VideoEvents]', 'U') IS NOT NULL
    DROP TABLE [dbo].[VideoEvents];
GO
IF OBJECT_ID(N'[dbo].[Products_Module]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Products_Module];
GO
IF OBJECT_ID(N'[dbo].[Products_Video]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Products_Video];
GO
IF OBJECT_ID(N'[dbo].[ModuleSpeakers]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ModuleSpeakers];
GO
IF OBJECT_ID(N'[dbo].[ProductProduct]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ProductProduct];
GO
IF OBJECT_ID(N'[dbo].[UserInteropProduct]', 'U') IS NOT NULL
    DROP TABLE [dbo].[UserInteropProduct];
GO
IF OBJECT_ID(N'[dbo].[OrderHistoryProduct]', 'U') IS NOT NULL
    DROP TABLE [dbo].[OrderHistoryProduct];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Venues'
CREATE TABLE [dbo].[Venues] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Address] nvarchar(max)  NOT NULL,
    [City] nvarchar(max)  NOT NULL,
    [State] nvarchar(max)  NOT NULL,
    [ZipCode] nvarchar(max)  NOT NULL,
    [Country] nvarchar(max)  NOT NULL,
    [LocalAccommodations] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Levels'
CREATE TABLE [dbo].[Levels] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Description] nvarchar(max)  NOT NULL,
    [HelpText] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'UserInterops'
CREATE TABLE [dbo].[UserInterops] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [UserName] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Feedback'
CREATE TABLE [dbo].[Feedback] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Comments] nvarchar(max)  NOT NULL,
    [UserInteropId] int  NOT NULL,
    [DatePosted] datetime  NOT NULL,
    [Product_Id] int  NOT NULL
);
GO

-- Creating table 'Products'
CREATE TABLE [dbo].[Products] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [Teaser] nvarchar(max)  NOT NULL,
    [Description] nvarchar(max)  NOT NULL,
    [Price] decimal(9,2)  NOT NULL,
    [Active] bit  NOT NULL,
    [Featured] bit  NOT NULL,
    [DatePosted] datetime  NOT NULL,
    [Deleted] bit  NOT NULL
);
GO

-- Creating table 'OrderHistories'
CREATE TABLE [dbo].[OrderHistories] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [DatePosted] datetime  NOT NULL,
    [PayPalResult] nvarchar(max)  NOT NULL,
    [PayPalTransaction] nvarchar(max)  NOT NULL,
    [TotalCost] decimal(9,2)  NOT NULL,
    [PayPalExtendedData] nvarchar(max)  NOT NULL,
    [UserInteropId] int  NOT NULL
);
GO

-- Creating table 'VideoEvents'
CREATE TABLE [dbo].[VideoEvents] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [DatePosted] datetime  NOT NULL,
    [EventName] nvarchar(max)  NOT NULL,
    [Video_Id] int  NOT NULL,
    [User_Id] int  NOT NULL
);
GO

-- Creating table 'TickerItems'
CREATE TABLE [dbo].[TickerItems] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Symbol] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Products_Module'
CREATE TABLE [dbo].[Products_Module] (
    [Start] datetime  NOT NULL,
    [End] datetime  NOT NULL,
    [MaxEnrollment] int  NOT NULL,
    [ContactEmail] nvarchar(max)  NOT NULL,
    [Id] int  NOT NULL,
    [Venue_Id] int  NOT NULL,
    [Level_Id] int  NOT NULL
);
GO

-- Creating table 'Products_Video'
CREATE TABLE [dbo].[Products_Video] (
    [Width] int  NOT NULL,
    [Height] int  NOT NULL,
    [FileName] nvarchar(max)  NOT NULL,
    [MimeType] nvarchar(max)  NOT NULL,
    [Id] int  NOT NULL
);
GO

-- Creating table 'ModuleSpeakers'
CREATE TABLE [dbo].[ModuleSpeakers] (
    [ModulesTaught_Id] int  NOT NULL,
    [Speakers_Id] int  NOT NULL
);
GO

-- Creating table 'ProductProduct'
CREATE TABLE [dbo].[ProductProduct] (
    [RelatedProducts_Product1_Id] int  NOT NULL,
    [RelatedProducts_Id] int  NOT NULL
);
GO

-- Creating table 'UserInteropProduct'
CREATE TABLE [dbo].[UserInteropProduct] (
    [Owners_Id] int  NOT NULL,
    [PurchasedProducts_Id] int  NOT NULL
);
GO

-- Creating table 'OrderHistoryProduct'
CREATE TABLE [dbo].[OrderHistoryProduct] (
    [OrderProducts_Product_Id] int  NOT NULL,
    [Products_Id] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'Venues'
ALTER TABLE [dbo].[Venues]
ADD CONSTRAINT [PK_Venues]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Levels'
ALTER TABLE [dbo].[Levels]
ADD CONSTRAINT [PK_Levels]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'UserInterops'
ALTER TABLE [dbo].[UserInterops]
ADD CONSTRAINT [PK_UserInterops]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Feedback'
ALTER TABLE [dbo].[Feedback]
ADD CONSTRAINT [PK_Feedback]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Products'
ALTER TABLE [dbo].[Products]
ADD CONSTRAINT [PK_Products]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'OrderHistories'
ALTER TABLE [dbo].[OrderHistories]
ADD CONSTRAINT [PK_OrderHistories]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'VideoEvents'
ALTER TABLE [dbo].[VideoEvents]
ADD CONSTRAINT [PK_VideoEvents]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'TickerItems'
ALTER TABLE [dbo].[TickerItems]
ADD CONSTRAINT [PK_TickerItems]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Products_Module'
ALTER TABLE [dbo].[Products_Module]
ADD CONSTRAINT [PK_Products_Module]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Products_Video'
ALTER TABLE [dbo].[Products_Video]
ADD CONSTRAINT [PK_Products_Video]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [ModulesTaught_Id], [Speakers_Id] in table 'ModuleSpeakers'
ALTER TABLE [dbo].[ModuleSpeakers]
ADD CONSTRAINT [PK_ModuleSpeakers]
    PRIMARY KEY NONCLUSTERED ([ModulesTaught_Id], [Speakers_Id] ASC);
GO

-- Creating primary key on [RelatedProducts_Product1_Id], [RelatedProducts_Id] in table 'ProductProduct'
ALTER TABLE [dbo].[ProductProduct]
ADD CONSTRAINT [PK_ProductProduct]
    PRIMARY KEY NONCLUSTERED ([RelatedProducts_Product1_Id], [RelatedProducts_Id] ASC);
GO

-- Creating primary key on [Owners_Id], [PurchasedProducts_Id] in table 'UserInteropProduct'
ALTER TABLE [dbo].[UserInteropProduct]
ADD CONSTRAINT [PK_UserInteropProduct]
    PRIMARY KEY NONCLUSTERED ([Owners_Id], [PurchasedProducts_Id] ASC);
GO

-- Creating primary key on [OrderProducts_Product_Id], [Products_Id] in table 'OrderHistoryProduct'
ALTER TABLE [dbo].[OrderHistoryProduct]
ADD CONSTRAINT [PK_OrderHistoryProduct]
    PRIMARY KEY NONCLUSTERED ([OrderProducts_Product_Id], [Products_Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [Venue_Id] in table 'Products_Module'
ALTER TABLE [dbo].[Products_Module]
ADD CONSTRAINT [FK_ModuleVenue]
    FOREIGN KEY ([Venue_Id])
    REFERENCES [dbo].[Venues]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ModuleVenue'
CREATE INDEX [IX_FK_ModuleVenue]
ON [dbo].[Products_Module]
    ([Venue_Id]);
GO

-- Creating foreign key on [Level_Id] in table 'Products_Module'
ALTER TABLE [dbo].[Products_Module]
ADD CONSTRAINT [FK_ModuleLevel]
    FOREIGN KEY ([Level_Id])
    REFERENCES [dbo].[Levels]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ModuleLevel'
CREATE INDEX [IX_FK_ModuleLevel]
ON [dbo].[Products_Module]
    ([Level_Id]);
GO

-- Creating foreign key on [UserInteropId] in table 'Feedback'
ALTER TABLE [dbo].[Feedback]
ADD CONSTRAINT [FK_FeedbackUserInterop]
    FOREIGN KEY ([UserInteropId])
    REFERENCES [dbo].[UserInterops]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_FeedbackUserInterop'
CREATE INDEX [IX_FK_FeedbackUserInterop]
ON [dbo].[Feedback]
    ([UserInteropId]);
GO

-- Creating foreign key on [ModulesTaught_Id] in table 'ModuleSpeakers'
ALTER TABLE [dbo].[ModuleSpeakers]
ADD CONSTRAINT [FK_ModuleSpeakers_Module]
    FOREIGN KEY ([ModulesTaught_Id])
    REFERENCES [dbo].[Products_Module]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Speakers_Id] in table 'ModuleSpeakers'
ALTER TABLE [dbo].[ModuleSpeakers]
ADD CONSTRAINT [FK_ModuleSpeakers_UserInterop]
    FOREIGN KEY ([Speakers_Id])
    REFERENCES [dbo].[UserInterops]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ModuleSpeakers_UserInterop'
CREATE INDEX [IX_FK_ModuleSpeakers_UserInterop]
ON [dbo].[ModuleSpeakers]
    ([Speakers_Id]);
GO

-- Creating foreign key on [Product_Id] in table 'Feedback'
ALTER TABLE [dbo].[Feedback]
ADD CONSTRAINT [FK_FeedbackProduct]
    FOREIGN KEY ([Product_Id])
    REFERENCES [dbo].[Products]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_FeedbackProduct'
CREATE INDEX [IX_FK_FeedbackProduct]
ON [dbo].[Feedback]
    ([Product_Id]);
GO

-- Creating foreign key on [RelatedProducts_Product1_Id] in table 'ProductProduct'
ALTER TABLE [dbo].[ProductProduct]
ADD CONSTRAINT [FK_RelatedProducts_Product]
    FOREIGN KEY ([RelatedProducts_Product1_Id])
    REFERENCES [dbo].[Products]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [RelatedProducts_Id] in table 'ProductProduct'
ALTER TABLE [dbo].[ProductProduct]
ADD CONSTRAINT [FK_RelatedProducts_Product1]
    FOREIGN KEY ([RelatedProducts_Id])
    REFERENCES [dbo].[Products]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_RelatedProducts_Product1'
CREATE INDEX [IX_FK_RelatedProducts_Product1]
ON [dbo].[ProductProduct]
    ([RelatedProducts_Id]);
GO

-- Creating foreign key on [Owners_Id] in table 'UserInteropProduct'
ALTER TABLE [dbo].[UserInteropProduct]
ADD CONSTRAINT [FK_UserInteropProduct_UserInterop]
    FOREIGN KEY ([Owners_Id])
    REFERENCES [dbo].[UserInterops]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [PurchasedProducts_Id] in table 'UserInteropProduct'
ALTER TABLE [dbo].[UserInteropProduct]
ADD CONSTRAINT [FK_UserInteropProduct_Product]
    FOREIGN KEY ([PurchasedProducts_Id])
    REFERENCES [dbo].[Products]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserInteropProduct_Product'
CREATE INDEX [IX_FK_UserInteropProduct_Product]
ON [dbo].[UserInteropProduct]
    ([PurchasedProducts_Id]);
GO

-- Creating foreign key on [UserInteropId] in table 'OrderHistories'
ALTER TABLE [dbo].[OrderHistories]
ADD CONSTRAINT [FK_UserInteropOrderHistory]
    FOREIGN KEY ([UserInteropId])
    REFERENCES [dbo].[UserInterops]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserInteropOrderHistory'
CREATE INDEX [IX_FK_UserInteropOrderHistory]
ON [dbo].[OrderHistories]
    ([UserInteropId]);
GO

-- Creating foreign key on [Video_Id] in table 'VideoEvents'
ALTER TABLE [dbo].[VideoEvents]
ADD CONSTRAINT [FK_VideoEventVideo]
    FOREIGN KEY ([Video_Id])
    REFERENCES [dbo].[Products_Video]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_VideoEventVideo'
CREATE INDEX [IX_FK_VideoEventVideo]
ON [dbo].[VideoEvents]
    ([Video_Id]);
GO

-- Creating foreign key on [User_Id] in table 'VideoEvents'
ALTER TABLE [dbo].[VideoEvents]
ADD CONSTRAINT [FK_VideoEventUserInterop]
    FOREIGN KEY ([User_Id])
    REFERENCES [dbo].[UserInterops]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_VideoEventUserInterop'
CREATE INDEX [IX_FK_VideoEventUserInterop]
ON [dbo].[VideoEvents]
    ([User_Id]);
GO

-- Creating foreign key on [OrderProducts_Product_Id] in table 'OrderHistoryProduct'
ALTER TABLE [dbo].[OrderHistoryProduct]
ADD CONSTRAINT [FK_OrderProducts_OrderHistory]
    FOREIGN KEY ([OrderProducts_Product_Id])
    REFERENCES [dbo].[OrderHistories]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Products_Id] in table 'OrderHistoryProduct'
ALTER TABLE [dbo].[OrderHistoryProduct]
ADD CONSTRAINT [FK_OrderProducts_Product]
    FOREIGN KEY ([Products_Id])
    REFERENCES [dbo].[Products]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_OrderProducts_Product'
CREATE INDEX [IX_FK_OrderProducts_Product]
ON [dbo].[OrderHistoryProduct]
    ([Products_Id]);
GO

-- Creating foreign key on [Id] in table 'Products_Module'
ALTER TABLE [dbo].[Products_Module]
ADD CONSTRAINT [FK_Module_inherits_Product]
    FOREIGN KEY ([Id])
    REFERENCES [dbo].[Products]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Id] in table 'Products_Video'
ALTER TABLE [dbo].[Products_Video]
ADD CONSTRAINT [FK_Video_inherits_Product]
    FOREIGN KEY ([Id])
    REFERENCES [dbo].[Products]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------